package org.hk.kg.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hk.kg.exception.EleParserException;
import org.hk.kg.exception.KGSQLException;
import org.hk.kg.utils.Constants;
import org.hk.kg.utils.LocalResouce;
import org.hk.kg.utils.StringUtils;
import org.hk.kg.utils.XMLUtils;
import org.jdom.Element;

public class DBContext implements IDBUtils{
	/**
	 * 数据库状态回调接口
	 * @author wangre
	 */
	public static interface DBStatusHandler{
		/**
		 * 
		 * @param dbname	
		 */
		void onBeginCreateDB(String dbname);
		
		/**
		 * 
		 * @param dbname
		 */
		void onCreateDBSuccess(String dbname);
		
		/**
		 * 
		 * @param dbname
		 */
		void onCreateDBFailure(String dbname);
		
		/**
		 * 
		 * @param dbname
		 */
		void onCreateTableSuccess(String tableNames);
		
		/**
		 * 
		 * @param dbname
		 */
		void onCreateTableFail(String tableNames,Throwable e);
	};
	
	private static final String DROP_TABLE_FLUSH = "drop table if exists %s";
	/***/
	private static final String CREATE_TABLE = "create table if not exists %s(";
	
	/***/
	private static final String CREATE_COLUMN = "%s %s";
	
	private static final Pattern SQL_EL_PATTERN = Pattern.compile("\\$\\{([^{|^}]*)\\}");
	
	private static final Pattern SQL_CHARGE_PATTERN = Pattern.compile("\\$([a-z|A-Z]*)\\(([^(|^)]*)\\)");
	
	public static DBContext instatnce = new DBContext();
	
	protected Map<String,Connection> connectMap = new HashMap<String,Connection>();
	
	/** 回调接口 */
	private DBStatusHandler handler;
	
	private static Log logger = LogFactory.getLog(DBContext.class);
	
	public DBContext()
	{
		try {
			Class.forName(Constants.DB_DRIVER);
			
		} catch (ClassNotFoundException e) {
			logger.error(e);
		}
	}
	
	public void load()
	{
		try {
		
			Element dbsElement = XMLUtils.getDocument(
					LocalResouce.getConfigFile(Constants.DB_CONFIG)).getRootElement();
			List dbElements = dbsElement.getChildren();
			for(Object dbEleObj : dbElements)
			{
				createDB((Element) dbEleObj);
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
	
	public void setHandler(DBStatusHandler handler) {
		this.handler = handler;
	}
	
	private void createDB(Element element)
	{
		StringBuffer createSQL = new StringBuffer();
		String dbname = element.getAttributeValue(Constants.DB_CONFIG_NAME_ATTR);
		handler.onBeginCreateDB(dbname);
		try {
			Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbname);
		
			List tableElements = element.getChildren();
			for(Object tableEleObj : tableElements)
			{
				Element tableEle = (Element) tableEleObj;
				Statement stat = conn.createStatement();
				String flush = tableEle.getAttributeValue(Constants.DB_CONFIG_FLUSH_ATTR),
					tableName = tableEle.getAttributeValue(Constants.DB_CONFIG_NAME_ATTR);
				if("true".equalsIgnoreCase(flush))
				{
					stat.execute(String.format(DROP_TABLE_FLUSH, tableName));
					logger.info("drop table:" + tableName);
				}
				
				String createSql = createTableSQL(tableEle);
				logger.info(createSql);
				try
				{
					stat.execute(createSql);
					handler.onCreateTableSuccess(tableName);
				}
				catch(Exception e)
				{
					handler.onCreateTableFail(tableName, e);
					continue;
				}
			}
			handler.onCreateDBSuccess(dbname);
			connectMap.put(dbname, conn);
			
			List sqlElement = element.getChildren(Constants.DB_CONFIG_SQL_TAG);
			for(Object sqlEleObj : sqlElement)
			{
				Element sqlEle = (Element) sqlEleObj;
				String sql = sqlEle.getText();
				this.execute(dbname, sql);
				logger.info("execute : " + sql);
			}
		} catch (SQLException e) {
			handler.onCreateDBFailure(dbname);
		}
	}

	/**
	 * 根据XML配置创建建表SQL
	 * @param element
	 * @return
	 */
	private String createTableSQL(Element element)
	{
		StringBuffer createSQL = new StringBuffer();
		
		//
		String tableName = element.getAttributeValue(Constants.DB_CONFIG_NAME_ATTR);
		createSQL.append(String.format(CREATE_TABLE, tableName));
		List colsElements = element.getChildren();
		List primaryKeys = new ArrayList();
		for(int idx = 0; idx < colsElements.size();idx++)
		{
			Element colEle = (Element) colsElements.get(idx);
			String colName = colEle.getAttributeValue(Constants.DB_CONFIG_NAME_ATTR),
				   colType = colEle.getAttributeValue(Constants.DB_CONFIG_TYPE_ATTR),
				   nullable = colEle.getAttributeValue(Constants.DB_CONFIG_APPLYNULL_ATTR),
				   primary = colEle.getAttributeValue(Constants.DB_CONGIG_PRIMARY_ATTR);
			
			String colSql = String.format(CREATE_COLUMN, colName,colType);
			if(!StringUtils.isEmpty(nullable) && nullable.equalsIgnoreCase("false"))
			{
				colSql+=" not null";
			}
			if(!StringUtils.isEmpty(primary)&&primary.equalsIgnoreCase("true"))
			{
				colSql+=" primary key";
			}
			if(idx < colsElements.size() - 1)
			{
				colSql+=",\n";
			}
			createSQL.append(colSql);
		}
		return createSQL.append(")").toString();
	}

	public int execute(String dbname, String sql){
		Connection conn = connectMap.get(dbname);
		Statement stat;
		try {
			stat = conn.createStatement();
			stat.executeUpdate(sql);
			return 1;
		} catch (SQLException e) {
			throw new KGSQLException("DBContext:execute ERROR",e);
		}
	}
	
	public int batchExecute(String dbname,String sqls[])
	{
		Connection conn = connectMap.get(dbname);
		Statement stat;
		try
		{
			stat = conn.createStatement();
			for(String sql : sqls)
			{
				stat.addBatch(sql);
			}
			stat.executeBatch();
			return 1;
		}
		catch(SQLException e)
		{
			throw new KGSQLException("DBContext:batchExecute ERROR",e);
		}
	}

	public List<Map> query(String dbname, String qrySql){
		Connection conn = connectMap.get(dbname);
		List<Map> rlt = new ArrayList<Map>();
		Statement stat;
		try {
			stat = conn.createStatement();
			ResultSet rltset = stat.executeQuery(qrySql);
			ResultSetMetaData metaData = rltset.getMetaData();
			int colCount = metaData.getColumnCount();
			while(rltset.next())
			{
				Map row = new HashMap();
				for(int colIdx = 1;colIdx<colCount+1;colIdx++)
				{
					String colName = metaData.getColumnName(colIdx);
					row.put(colName, rltset.getObject(colName));
				}
				rlt.add(row);
			}
			return rlt;
		} catch (SQLException e) {
			throw new KGSQLException("DBContext:execute ERROR",e);
		}
		
		
	}

	public Map querySingle(String dbname, String qrySql) {
		Connection conn = connectMap.get(dbname);
		Statement stat;
		try {
			stat = conn.createStatement();
			ResultSet rltset = stat.executeQuery(qrySql);
			ResultSetMetaData metaData = rltset.getMetaData();
			int colCount = metaData.getColumnCount();
			Map row = new HashMap();
			if(rltset.next())
			{
				for(int colIdx = 1;colIdx<colCount+1;colIdx++)
				{
					String colName = metaData.getColumnName(colIdx);
					row.put(colName, rltset.getObject(colName));
				}
			}
			if(rltset.next())
			{
				throw new KGSQLException("DBContext:querySingle have more than one result!");
			}
			return row;
		} catch (SQLException e) {
			throw new KGSQLException("DBContext:querySingle ERROR",e);
		}	
	}

	public List<Map> query(String dbname, String qrySql, Map qryMap) {
		String sql = parse2SQL(parseEl(qrySql, qryMap),qryMap);
		logger.info("query : " + sql);
		return query(dbname,sql);
	}

	public int execute(String dbname, String sql, Map uptMap) {
		return execute(dbname,parse2SQL(parseEl(sql, uptMap), uptMap));
	}
	
	/**
	 * 将包含${attr}的sql替换为对应map.get(attr)后的sql
	 * @param tplSQL
	 * @param map
	 * @return
	 */
	private static String parse2SQL(String tplSQL,Map map)
	{
		Matcher matcher = SQL_EL_PATTERN.matcher(tplSQL);
		while(matcher.find())
		{
			Object value = map.get(matcher.group(1));
			if(value == null)
			{
				value = "";
			}
			tplSQL = matcher.replaceFirst(value.toString());
			matcher.reset(tplSQL);
		}
		return tplSQL;
	}
	
	private static String parseEl(String qry,Map map)
	{
		Matcher matcher = SQL_CHARGE_PATTERN.matcher(qry);
		while(matcher.find())
		{
			String method = matcher.group(1),
				params = matcher.group(2);
			if(method.equals("notNull"))
			{
				String[] paramsAry = params.split("\\,");
				String chargeKey = paramsAry[0];
				if(map.containsKey(chargeKey) && map.get(chargeKey)!=null)
				{
					Object objValue = map.get(chargeKey);
					if(!StringUtils.isEmpty(objValue.toString()))
					{
						qry = qry.replace(matcher.group(0),paramsAry[1]);
					}
					else
					{
						qry = matcher.replaceFirst("");
					}
				}
				else
				{
					qry = matcher.replaceFirst("");
				}
				
				matcher.reset(qry);
			}
		}
		return qry;
	}
}
